package net.scrumtool.technics;

import hibernate.HibernateUtil;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Set;

import net.scrumtool.model.Collaborator;
import net.scrumtool.model.Event;
import net.scrumtool.model.Eventtype;
import net.scrumtool.model.Participate;
import net.scrumtool.model.Playrole;
import net.scrumtool.model.Product;
import net.scrumtool.model.Role;
import net.scrumtool.model.Sprint;
import net.scrumtool.model.Status;
import net.scrumtool.model.Userstory;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

public abstract class DAO {

	private static Collaborator col;

	public static List<Collaborator> getUsers() {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();
		Query query = session.createQuery("from Collaborator");
		List<Collaborator> users = query.list();
		trans.commit();
		session.close();
		return users;
	}

	// SPRINTS
	public static void SQLCollaborator(Collaborator collaborator, String type) {

		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();
		if (type == "insert") {
			session.persist(collaborator);
		} else if (type == "update") {
			session.update(collaborator);
		} else if (type == "delete") {
			session.delete(collaborator);
		}
		trans.commit();
		session.close();

	}

	public static List<Product> getProductByCollaborator(Collaborator collaborator) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = "SELECT p "
				+ "FROM Product AS p "
				+ "JOIN p.playroles AS pr "
				+ "JOIN pr.collaborator AS c "
				+ "WHERE c.id=" + collaborator.getId();

		Query query = session.createQuery(sql);
		List<Product> products = query.list();
		trans.commit();
		session.close();
		return products;
	}

	public static void SQLProduct(Product product, String type) {

		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();
		if (type == "insert") {
			session.persist(product);
		} else if (type == "update") {
			session.update(product);
		} else if (type == "delete") {
			session.delete(product);
		}
		trans.commit();
		session.close();

	}

	public static void SQLSprint(Sprint sprint, String type) {

		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();
		if (type == "insert") {
			session.persist(sprint);
		} else if (type == "update") {
			session.update(sprint);
		} else if (type == "delete") {
			session.delete(sprint);
		}
		trans.commit();
		session.close();

	}

	public static void SQLUserStory(Userstory userstory, String type) {

		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();
		if (type == "insert") {
			session.persist(userstory);
		} else if (type == "update") {
			session.update(userstory);
		} else if (type == "delete") {
			session.delete(userstory);
		}
		trans.commit();
		session.close();

	}

	public static List<Sprint> getSprints() {

		return getSprints("");
	}

	public static List<Sprint> getSprints(String condition) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		Query query = session.createQuery("from Sprint " + condition);

		List<Sprint> sprints = query.list();

		trans.commit();
		session.close();

		return sprints;
	}

	public static List<Sprint> getSprintsByNameProduct(String nameProduct) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		Query query = session.createQuery("select s from Sprint AS s JOIN s.product AS pr WHERE pr.name='" + nameProduct + "'");

		List<Sprint> sprints = query.list();

		trans.commit();
		session.close();

		return sprints;
	}

	public static List<Sprint> getSprintsRecent(Product product) {

		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = " SELECT s FROM Sprint AS s"
				+ " JOIN s.events AS e"
				+ " JOIN e.eventtype AS et"
				+ " JOIN s.product AS p"
				+ " WHERE et.id=2"
				+ " AND (DATEDIFF(NOW(),e.eventDate) <=7)"
				+ " AND (DATEDIFF(NOW(),e.eventDate) >=0)"
				+ " AND p.id=" + product.getId();

		Query query = session.createQuery(sql);
		List<Sprint> sprints = query.list();

		trans.commit();
		session.close();

		return sprints;
	}

	public static List<Userstory> getUserstoryToDo(Product product) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = " SELECT u FROM Userstory AS u"

				+ " JOIN u.status AS st"
				+ " JOIN u.product AS p"
				+ " WHERE st.idStatus=1"
				+ " AND p.id=" + product.getId();

		Query query = session.createQuery(sql);
		List<Userstory> userstory = query.list();

		trans.commit();
		session.close();

		return userstory;
	}

	public static List<Userstory> getUserstoryInProgress(Product product) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = " SELECT u FROM Userstory AS u"
				+ " JOIN u.status AS st"
				+ " JOIN u.product AS p"
				+ " WHERE st.idStatus=2"
				+ " AND p.id=" + product.getId();

		Query query = session.createQuery(sql);
		List<Userstory> userstory = query.list();

		trans.commit();
		session.close();

		return userstory;
	}

	public static List<Userstory> getUserstoryDone(Product product) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = " SELECT u FROM Userstory AS u"
				+ " JOIN u.status AS st"
				+ " JOIN u.product AS p"
				+ " WHERE st.idStatus=3"
				+ " AND p.id=" + product.getId()
				+ " ORDER BY u.priority";

		Query query = session.createQuery(sql);
		List<Userstory> userstory = query.list();

		trans.commit();
		session.close();

		return userstory;
	}

	public static List<Date> getDatesOfSprint(Sprint sprint) {

		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = "SELECT ev.eventDate FROM Sprint AS s "
				+ "JOIN s.events AS ev "
				+ "WHERE s.id=" + sprint.getId();
		Query query = session.createQuery(sql);

		List<Date> dates = query.list();

		trans.commit();
		session.close();

		return dates;

	}

	public static Date getDateOfSprint(Sprint sprint, String type) {
		int id = 1;
		if (type == "start") {
			id = 1;
		} else if (type == "end")
			id = 2;
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = "SELECT ev.eventDate FROM Sprint AS s "
				+ "JOIN s.events AS ev "
				+ "JOIN ev.eventtype AS evt "
				+ "WHERE s.id=" + sprint.getId() + " AND evt.id=" + id + "";
		Query query = session.createQuery(sql);

		Date date = (Date) query.uniqueResult();

		trans.commit();
		session.close();

		return date;
	}

	public static int getNbSprint(Product produit) {
		Set<Sprint> sprints = produit.getSprints();
		return sprints.size();
	}

	public static int getNbPoints(Product produit) {
		int nb = 0;
		Set<Userstory> userstory = produit.getUserstories();
		for (Userstory users : userstory) {
			nb = nb + users.getStoryPoints();
		}
		return nb;
	}

	// STATUS
	public static List<Status> getStatus() {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		Query query = session.createQuery("from Status");

		List<Status> status = query.list();

		trans.commit();
		session.close();

		return status;
	}

	// COLLABORATORS
	public static List<Collaborator> getCollaborators() {

		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		Query query = session.createQuery("from Collaborator");

		List<Collaborator> collaborators = query.list();

		trans.commit();
		session.close();

		return collaborators;
	}

	public static List<Collaborator> getCollaboratorsIntoProduct(Product product) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = "SELECT c "
				+ "FROM Product AS p "
				+ "JOIN p.playroles AS pr "
				+ "JOIN pr.role AS r "
				+ "JOIN pr.collaborator AS c "
				+ "WHERE p.id=" + product.getId();

		Query query = session.createQuery(sql);

		List<Collaborator> collaborators = query.list();

		trans.commit();
		session.close();

		return collaborators;
	}

	public static List<Playrole> getPlayroleByProduct(Product product) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = "";

		Query query = session.createQuery("SELECT pr from Playrole AS pr JOIN pr.product AS pro WHERE pro.id=" + product.getId());

		List<Playrole> playrole = query.list();

		trans.commit();
		session.close();

		if (playrole.size() > 0) {
			return playrole;
		}

		return null;

	}

	public static List<Participate> getParticipateBySprint(Sprint sprint) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = "";

		Query query = session.createQuery("SELECT pr from "
				+ "Participate AS pr "
				+ "JOIN pr.sprint AS sp "
				+ "WHERE sp.id=" + sprint.getId());

		List<Participate> participate = query.list();

		trans.commit();
		session.close();

		if (participate.size() > 0) {
			return participate;
		}

		return null;
	}

	public static List<Collaborator> getCollaboratorsNotInProduct(Product product) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = "SELECT * FROM collaborator WHERE id NOT IN (SELECT idCollaborator FROM playrole WHERE idProduct=" + product.getId() + ")";

		Query query = session.createSQLQuery(sql).addEntity(Collaborator.class);

		List<Collaborator> collaborators = query.list();

		trans.commit();
		session.close();

		if (collaborators.size() > 0) {
			return collaborators;
		}
		return null;
	}

	public static List<Collaborator> getCollaboratorsNotInSprint(Sprint sprint) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = "SELECT * FROM collaborator WHERE id NOT IN (SELECT idCollaborator FROM participate WHERE idSprint=" + sprint.getId() + ") AND id IN (SELECT idCollaborator FROM playrole WHERE idProduct=" + sprint.getProduct().getId() + ")";

		Query query = session.createSQLQuery(sql).addEntity(Collaborator.class);

		List<Collaborator> collaborators = query.list();

		trans.commit();
		session.close();

		if (collaborators.size() > 0) {
			return collaborators;
		}
		return null;
	}

	public static Playrole getPlayrole(Collaborator collaborator, Product product) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = "SELECT *"
				+ " FROM playrole"
				+ " WHERE idCollaborator=" + collaborator.getId()
				+ " AND idProduct=" + product.getId();

		Query query = session.createSQLQuery(sql).addEntity(Playrole.class);

		List<Playrole> playrole = query.list();
		trans.commit();
		session.close();

		if (playrole.size() > 0) {
			return playrole.get(0);
		}

		return null;
	}

	public static List<Role> getRoleWhereNameIsNot(String name) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = "SELECT r from Role AS r WHERE r.name!='" + name + "'";

		Query query = session.createQuery(sql);

		List<Role> role = query.list();
		trans.commit();
		session.close();

		return role;

	}

	public static List<Role> getRole() {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = "from Role";

		Query query = session.createQuery(sql);

		List<Role> role = query.list();
		trans.commit();
		session.close();

		return role;
	}

	public static void addPlayrole(Playrole playrole) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		// PlayroleId playroleId = new PlayroleId(collaborator.getId(),
		// role.getId(), product.getId());
		// Playrole playrole = new Playrole(playroleId, role, product,
		// collaborator);
		// dd
		session.persist(playrole);

		trans.commit();
		session.close();
	}

	public static void addParticipate(Participate participate) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		session.persist(participate);

		trans.commit();
		session.close();
	}

	public static void delPlayrole(Playrole playrole) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		session.delete(playrole);

		trans.commit();
		session.close();

	}

	public static void delParticipate(Participate participate) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		session.delete(participate);

		trans.commit();
		session.close();
	}

	// PRODUCTS
	public static List<Product> getProducts() {

		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		Query query = session.createQuery("from Product");

		List<Product> products = query.list();

		trans.commit();
		session.close();

		return products;
	}

	public static Product getProductByName(String name) {

		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		Query query = session.createQuery("from Product where name='" + name + "'");

		Product product = (Product) query.uniqueResult();

		trans.commit();
		session.close();

		return product;
	}

	// PRODUCT BACKLOG

	// EVENT

	public static void SQLEvent(Event event, String type) {

		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();
		if (type == "insert") {
			session.persist(event);
		} else if (type == "update") {
			session.update(event);
		} else if (type == "delete") {
			session.delete(event);
		}
		trans.commit();
		session.close();

	}

	public static void SQLEventtype(Eventtype eventtype, String type) {

		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();
		if (type == "insert") {
			session.persist(eventtype);
		} else if (type == "update") {
			session.update(eventtype);
		} else if (type == "delete") {
			session.delete(eventtype);
		}
		trans.commit();
		session.close();

	}

	public static List<Event> getEventsByProduct(String nameProduct) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		String sql = "SELECT e FROM Product AS p "
				+ "JOIN p.sprints AS s "
				+ "JOIN s.events AS e "
				+ "WHERE p.name='" + nameProduct + "'";
		Query query = session.createQuery(sql);

		List<Event> events = query.list();

		trans.commit();
		session.close();

		return events;
	}

	public static List<Event> getEvents(String condition) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		Query query = session.createQuery("from Event " + condition);

		List<Event> events = query.list();

		trans.commit();
		session.close();

		return events;
	}

	public static List<Event> getEvents() {
		return getEvents("");
	}

	public static List<Eventtype> getEventType(String condition) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		Query query = session.createQuery("from Eventtype " + condition);

		List<Eventtype> eventtypes = query.list();

		trans.commit();
		session.close();

		return eventtypes;
	}

	public static List<Eventtype> getEventType() {
		return getEventType("");
	}

	public static void checkEventtype() {
		List<Eventtype> eventtypes = new ArrayList<>();

		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		Query query = session.createQuery("from Eventtype");

		eventtypes = query.list();

		if (eventtypes.size() <= 1) {
			for (Eventtype ev : eventtypes) {
				SQLEventtype(ev, "delete");
			}
			Eventtype ev1 = new Eventtype();
			Eventtype ev2 = new Eventtype();
			ev1.setLabel("StartSprint");
			ev2.setLabel("EndSprint");

			SQLEventtype(ev1, "insert");
			SQLEventtype(ev2, "insert");
		}

		trans.commit();
		session.close();

	}

	// STATUS

	/*
	 * public static void checkStatus(){ List<Status> liststatus = new
	 * ArrayList<>();
	 * 
	 * Session session = HibernateUtil.getSession(); Transaction trans =
	 * session.beginTransaction();
	 * 
	 * Query query = session.createQuery("from Status");
	 * 
	 * status = query.list();
	 * 
	 * if (eventtypes.size() <= 1) { for (Eventtype ev : eventtypes) {
	 * SQLEventtype(ev, "delete"); } Eventtype ev1 = new Eventtype(); Eventtype
	 * ev2 = new Eventtype(); ev1.setLabel("StartSprint");
	 * ev2.setLabel("EndSprint");
	 * 
	 * SQLEventtype(ev1, "insert"); SQLEventtype(ev2, "insert"); }
	 * 
	 * trans.commit(); session.close(); }
	 */

	// USERS STORIES
	public static List<Userstory> getUsersStories(Sprint sprint) {
		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		Query query = session.createQuery("SELECT us FROM Userstory AS us "
				+ "JOIN us.sprint AS s "
				+ "WHERE s.id=" + sprint.getId());

		List<Userstory> usersstories = query.list();

		trans.commit();
		session.close();

		return usersstories;
	}

	private static Integer getPoints(List<Userstory> usersstories, Sprint sprint) {
		Integer points = 0;
		if (usersstories.size() > 0) {
			points = countPoints(usersstories);
		} else if (sprint.getId() > 0) {
			Session session = HibernateUtil.getSession();
			Transaction trans = session.beginTransaction();

			Query query = session.createQuery("SELECT us FROM Userstory AS us "
					+ "JOIN us.sprint AS s "
					+ "WHERE s.id=" + sprint.getId());

			usersstories = query.list();

			points = countPoints(usersstories);

			trans.commit();
			session.close();
		} else {
			points = 0;
		}
		return points;
	}

	public static Integer getPoints(Sprint sprint) {
		List<Userstory> usersstories = new ArrayList<>();
		return getPoints(usersstories, sprint);
	}

	/**
	 * Ligther with userstories in parameter
	 * 
	 * @param usersstories
	 * @return Integer points
	 */
	public static Integer getPoints(List<Userstory> usersstories) {
		Sprint sprint = new Sprint();
		return getPoints(usersstories, sprint);
	}

	public static int finishByPercent(Set<Userstory> usersstories) {
		int n = 0;
		for (Userstory us : usersstories) {
			if (us.getStatus().getIdStatus() == 3) {
				n++;
			}
		}

		float percent = (float) n / usersstories.size();
		percent = percent * 100;
		int nb = (int) percent;
		return nb;
	}

	private static int countPoints(List<Userstory> usersstories) {
		int points = 0;
		for (Userstory us : usersstories) {
			points = points + us.getStoryPoints();
		}
		return points;
	}

	// CONNEXION
	public static Collaborator getInfosUser(String login, String password) {

		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		Query query = session.createQuery("from Collaborator where login='" + login + "' and password='" + password + "' ");

		List<Collaborator> user = query.list();

		Collaborator col = new Collaborator();
		for (Collaborator cola : user) {
			col.setId(cola.getId());
			col.setLogin(cola.getLogin());
			col.setPassword(cola.getPassword());
			col.setFirstname(cola.getFirstname());
			col.setLastname(cola.getLastname());
			col.setEmail(cola.getEmail());
			col.setAdministrator(cola.getAdministrator());

		}

		trans.commit();
		session.close();

		return col;
	}

	public static Boolean isExistUser(String login, String password) {

		Session session = HibernateUtil.getSession();
		Transaction trans = session.beginTransaction();

		Query query = session.createQuery("from Collaborator where login='" + login + "' and password='" + password + "' ");

		List<Collaborator> users = query.list();

		trans.commit();
		session.close();

		if (users.size() > 0) {
			return true;
		} else {
			return false;
		}

	}

}
